package powell.nflpredictionproject.database;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 *
 * @author Joshua Powell
 */
public class BuildDatabase
{

    public void dropTables()
    {
        System.out.println("Checking for existing tables.");

        try
        {
            Connection conn = DriverManager.getConnection("jdbc:derby:NFLStatsDatabase;create=true");
            // Get a Statement object.
            Statement stmt = conn.createStatement();;

            try
            {
                // Drop the UnpaidOrder table.
                stmt.execute("DROP TABLE Stats2012");
                System.out.println("Stats table dropped.");
                stmt.execute("DROP TABLE Stats2013");
                System.out.println("Stats table dropped.");
            } catch (SQLException ex)
            {
                // No need to report an error.
                // The table simply did not exist.
            }
        } catch (SQLException ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
            ex.printStackTrace();
        }
    }

    public void BuildStatsTables() throws FileNotFoundException, SQLException
    {
        File inFile = new File("Stats.txt");
        Scanner readFile = new Scanner(inFile);
        String[] statCategories = readFile.nextLine().split(",");


        Connection conn = DriverManager.getConnection("jdbc:derby:NFLStatsDatabase;create=true");
        Statement stmt = conn.createStatement();

        String createTable2012 = "CREATE TABLE Stats2012("
                + statCategories[1] + " CHAR(25) Default Null, "
                + statCategories[2] + " int Default Null, "
                + statCategories[3] + " int Default Null, "
                + statCategories[4] + " CHAR(25) Default Null, "
                + statCategories[5] + " int Default Null, "
                + statCategories[6] + " int Default Null, "
                + statCategories[7] + " int Default Null, "
                + statCategories[8] + " int Default Null, "
                + statCategories[9] + " int Default Null, "
                + statCategories[10] + " int Default Null, "
                + statCategories[11] + " DECIMAL Default Null, "
                + statCategories[12] + " int Default Null, "
                + statCategories[13] + " int Default Null, "
                + statCategories[14] + " int Default Null, "
                + statCategories[15] + " CHAR(25) Default Null, "
                + statCategories[16] + " float Default Null"
                + ")";
        stmt.execute(createTable2012);
        
        String createTable2013 = "CREATE TABLE Stats2013("
                + statCategories[1] + " CHAR(25) Default Null, "
                + statCategories[2] + " int Default Null, "
                + statCategories[3] + " int Default Null, "
                + statCategories[4] + " CHAR(25) Default Null, "
                + statCategories[5] + " int Default Null, "
                + statCategories[6] + " int Default Null, "
                + statCategories[7] + " int Default Null, "
                + statCategories[8] + " int Default Null, "
                + statCategories[9] + " int Default Null, "
                + statCategories[10] + " int Default Null, "
                + statCategories[11] + " DECIMAL Default Null, "
                + statCategories[12] + " int Default Null, "
                + statCategories[13] + " int Default Null, "
                + statCategories[14] + " int Default Null, "
                + statCategories[15] + " CHAR(25) Default Null, "
                + statCategories[16] + " float Default Null"
                + ")";
        stmt.execute(createTable2013);

        conn.close();
    }
}
